﻿using System;
using System.Data.SqlClient;
using OpusSuite.Utility;
using System.Data.Common;
using OpusSuite.Models.Lib;
using MvcMiniProfiler;

namespace OpusSuite.Models.Providers
{
    class SqlOpusSuiteProvider : IOpusSuiteProvider
    {
        private readonly DbConnection _connection;
        public DbConnection Connection { get { return _connection; } }

        public string Name { get; set; }

        public string Version { get; set; }

        public SqlOpusSuiteProvider(string connectionString)
        {
            var cnn = new SqlConnection(connectionString);

            // wrap the connection with a profiling connection that tracks timings 
            _connection = new MvcMiniProfiler.Data.EFProfiledDbConnection(cnn, MiniProfiler.Current);

            //_connection = cnn;
        }

        public bool TryDbConnection(string userName, string password, string databaseName, string serverName)
        {
            string connectionString = ConnectionString(userName, password, databaseName, serverName);

            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    connection.Close();
                    return true;
                }
            }
            catch
            {
            }

            return false;
        }

        private bool TryDBConnection(string sConnection)
        {
            try
            {
                using (var connection = new SqlConnection(sConnection))
                {
                    connection.Open();
                    connection.Close();
                    return true;
                }
            }
            catch
            {
            }

            return false;
        }

        public bool SetupCreateDatabase(OpusSuiteModel model, string dbUserName, string dbPassword, string dbDatabase, string dbServer, string userName)
        {
            string s = ConnectionString(dbUserName, dbPassword, dbDatabase, dbServer);
            string sCheck = ConnectionString(dbUserName, dbPassword, "master", dbServer);

            try
            {
                // Causes database to be generated using Code First
                if (TryDBConnection(sCheck))
                {
                    using (var connection = new SqlConnection(sCheck))
                    {
                        connection.Open();

                        try
                        {
                        SqlCommand c = new SqlCommand("create login " + userName + " with password='" + userName + "'",
                                                      connection);
                        c.ExecuteNonQuery();

                        c.CommandText =
                            "grant SELECT, UPDATE, INSERT, BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW to " +
                            userName + " with grant option";
                        c.ExecuteNonQuery();
                        }
                        catch
                        {
                            // ignore already exists.  TODO
                        }

                        connection.Close();
                    }

                    // Make sure Code First creates the database here
                    model.Context.CreateDatabase(dbUserName, dbPassword, dbDatabase, dbServer, Enums.ServerTypes.SqlServer);

                    using (var connection = new SqlConnection(s))
                    {
                        connection.Open();

                        SqlCommand c = new SqlCommand("create user " + userName + " for login " + userName,
                                                      connection);
                        c.ExecuteNonQuery();

                        c.CommandText =
                            "grant SELECT, UPDATE, INSERT, BACKUP DATABASE, BACKUP LOG, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW to " +
                            userName + " with grant option";
                        c.ExecuteNonQuery();

                        connection.Close();
                    }

                    return true;
                }
            }
            catch (Exception e)
            {
                throw new ModelException("SQL Server: Unable to create database schema: ", e);                
            }

            return false;
        }

        public string ConnectionString(string userName, string password, string databaseName, string serverName)
        {
            return "Data Source=" + serverName + ";Initial Catalog=" + databaseName + ";User ID=" + userName + ";Password=" + password;
        }

        public bool ChangePassword(string userName, string newPassword)
        {
            try
            {
                _connection.Open();

                SqlCommand c = new SqlCommand("ALTER LOGIN " + userName + " WITH PASSWORD = '" + newPassword + "'", (SqlConnection)_connection);
                c.ExecuteNonQuery();

                _connection.Close();
            }
            catch (Exception e)
            {
                throw new ModelException("Cannot change password for " + userName + ": ", e);
            }

            return true;
        }
    }
}
